package com.itbaizhan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**动态查询
 * 根据用户给定的条件来决定执行什么样的查询*/
//会有sql注入风险  可以用数据检验，如果有sql语句片段就不执行，注要是String类型
public class DynamicConditionQuery {

    //可能会有多条数据返加，所以用集合
    public List<Users> queryUsers(Users users){
        List<Users> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            //获取Connection对象
            connection = JdbcUtils.getConnection();
            //创建PreparedStatement对象
            //拼接查询Sql语句，新建一个方法generateSql()写语句
            //根据传过来的users选择语句查询
            String sql = this.generateSql(users);
            ps = connection.prepareStatement(sql);
            //执行sql
            rs = ps.executeQuery();
            while (rs.next()){
                Users users1 = new Users();
                users1.setUserid(rs.getInt("userid"));
                users1.setUsername(rs.getString("username"));
                users1.setUsergae(rs.getInt("userage"));
                list.add(users1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.closeResource(rs,ps,connection);
        }
        return list;
    }
    //生成动态条件查询sql
    private String generateSql(Users users){
        //字符串拼接
        /*where 后加1=1，那么是一定成立的，如果没有条件就是查询所有数据，如果后面
        * 有条件，也可以用and 来连接*/
        //注意：每个单词之间一定要用空格键隔开
        StringBuffer sb = new StringBuffer("select * from users where 1=1 ");
        if(users.getUserid() > 0){
            sb.append(" and userid = ").append(users.getUserid());

        }

        if(users.getUsername() != null && users.getUsername().length() > 0){
            //字符串类型要有单引号  除了PreparedStatement 不用加，其它都要加单引号
            sb.append(" and username = '").append(users.getUsername()).append("'");

        }
        if(users.getUsergae() > 0){
            sb.append(" and userage = ").append(users.getUsergae());
        }
        return sb.toString();
    }

    public static void main(String[] args) {
        DynamicConditionQuery dcq = new DynamicConditionQuery();
        Users users = new Users();
        List<Users> list = dcq.queryUsers(users);//现在是查所有数据
        for(Users users2:list){
            System.out.println(users2.getUserid()+" "+users2.getUsername()+" "+ users2.getUsergae());
        }

    }
}
